<?php

	abstract class AbstractDb {

		private $_reservedParameters = array( "order", "count", "sortOrder", "sortColumn", "pageNumber", "quantity", 'sqlFunction' );

		private $_sortOrder = "DESC";
		private $_sortColumn = false;
		private $_pageNumber = 1;
		protected $_quantity = 10;
		private $_count = 'N';
		private $_order = 'Y';
		private $_validSqlFunctions = array( 'count', 'sum', 'avg' );
		private $_sqlFunctions = array();
		protected $_enableForcedCondition = false;
		protected $_betweenClause = '';
		protected $_databaseConnection = null;
		protected $_mainTable = null;
		protected $_clauseColumnNames = null;
		protected $_sortColumnNames = array();
		protected $_requiredAddColumns = array();
		protected $_runtimeAddColumns = array();
		protected $_foreignKey = null;
		protected $_expandableTables = null;
		private $_mainTablePermissibleValues = '';
		private $_expandableTablesPermissibleValues = '';
		private $_arrayExpandablePermissibleValues = array();
		private $_arrayClauses = array();
		protected $_lastInsertedId = 0;
		protected $_arrayUpdatedData = array();
		private $_task = 'show';
		private $_dataResultArray = array();
		private $_dataResultCount = 0;
		private $_dataTotalCount = 0;
		private $_dataTotalPages = 0;

		public function setForcedCondition( $betweenClause ) {
			$this->_betweenClause = $betweenClause;
			if( !empty( $betweenClause ) ) {
				$this->_enableForcedCondition = true;
			}
		}

		public function __construct( $task = 'show' ) {
			$this->_task = strtolower( $task );
			$this->_databaseConnection = Connection::GetInstance();
		}

		public function set() {
			try {
				$argumentsList = func_get_args();
				$totalArguments = count( $argumentsList );
				switch( $this->_task ) {
					case 'add': {
						for( $i = 0;$i < $totalArguments; $i++ ) {
							$tempArgumentArray = explode( '||', $argumentsList[ $i ] );
							$argumentsArrayCount = count( $tempArgumentArray );
							if( $argumentsArrayCount < 2 ) {
								throw new Exception( 'Abstract : Argument ' . $argumentsList[ $i ] . ' is malformed!' );
							} else if( $argumentsArrayCount > 2 ) {
								for( $ii = 2; $ii < $argumentsArrayCount; $ii++ ) {
									$tempArgumentArray[ 1 ] .= '||' . $tempArgumentArray[ $ii ];
								}
							}
							$this->_arrayUpdatedData[ $tempArgumentArray[ 0 ] ] = $tempArgumentArray[ 1 ];
						}

						$this->add();
						break;
					} case 'edit': {
						for( $i = 0;$i < $totalArguments; $i++ ) {
							$tempArgumentArray = explode( '||', $argumentsList[ $i ] );
							$argumentsArrayCount = count( $tempArgumentArray );
							if( $argumentsArrayCount < 2 ) {
								throw new Exception( 'Abstract : Argument ' . $argumentsList[ $i ] . ' is malformed!' );
							} else if( $argumentsArrayCount > 2 ) {
								for( $ii = 2; $ii < $argumentsArrayCount; $ii++ ) {
									$tempArgumentArray[ 1 ] .= '||' . $tempArgumentArray[ $ii ];
								}
							}
							$this->_arrayUpdatedData[ $tempArgumentArray[ 0 ] ] = $tempArgumentArray[ 1 ];
						}

						$this->edit();
						break;
					} case 'delete': {
						for( $i = 0;$i < $totalArguments; $i++ ) {
							$tempArgumentArray = explode( '||', $argumentsList[ $i ] );
							$argumentsArrayCount = count( $tempArgumentArray );
							if( $argumentsArrayCount != 2 ) {
								throw new Exception( 'Abstract : Argument ' . $argumentsList[ $i ] . ' is malformed!' );
							}
							$this->_arrayUpdatedData[ $tempArgumentArray[ 0 ] ] = $tempArgumentArray[ 1 ];
						}

						$this->delete();
						break;
					} default: {
						for( $i = 0;$i < $totalArguments; $i++ ) {
							$tempArgumentArray = explode( '||', $argumentsList[ $i ] );
							if( count( $tempArgumentArray ) != 2 ) {
								throw new Exception( 'Abstract : Argument ' . $argumentsList[ $i ] . ' is malformed!' );
							}

							if( array_search( $tempArgumentArray[ 0 ], $this->_reservedParameters ) === FALSE ) {
								$firstCharacter = substr( $tempArgumentArray[ 1 ], 0, 1 );
								if( '=' == $firstCharacter || '<' == $firstCharacter || '>' == $firstCharacter ) {
									$tempArgumentArray[1] = substr( $tempArgumentArray[ 1 ], 1 );
									$clauseValue = escape( $tempArgumentArray[ 1 ] );
									if( is_numeric( $clauseValue ) || strtolower( ( trim( $clauseValue ) ) ) == 'now()' ) {
										$tempClause = "`$tempArgumentArray[0]` $firstCharacter $clauseValue";
									} else {
										$tempClause = "`$tempArgumentArray[0]` $firstCharacter '$clauseValue'";
									}
								} else {
									if( substr( $tempArgumentArray[ 1 ], 0, 11 ) == '{%notlike%}' ) {
										$clauseValue = escape( substr( $tempArgumentArray[ 1 ], 11 ) );
										$tempClause = "`$tempArgumentArray[0]` not like '%$clauseValue%'";
									} else if( substr( $tempArgumentArray[ 1 ], 0, 10 ) == '{notlike%}' ) {
										$clauseValue = escape( substr( $tempArgumentArray[ 1 ], 10 ) );
										$tempClause = "`$tempArgumentArray[0]` not like '$clauseValue%'";
									} else if( substr( $tempArgumentArray[ 1 ], 0, 10 ) == '{%notlike}' ) {
										$clauseValue = escape( substr( $tempArgumentArray[ 1 ], 10 ) );
										$tempClause = "`$tempArgumentArray[0]` not like '%$clauseValue'";
									} else if( substr( $tempArgumentArray[ 1 ], 0, 8 ) == '{%like%}' ) {
										$clauseValue = escape( substr( $tempArgumentArray[ 1 ], 8 ) );
										$tempClause = "`$tempArgumentArray[0]` like '%$clauseValue%'";
									} else if( substr( $tempArgumentArray[ 1 ], 0, 7 ) == '{like%}' ) {
										$clauseValue = escape( substr( $tempArgumentArray[ 1 ], 7 ) );
										$tempClause = "`$tempArgumentArray[0]` like '$clauseValue%'";
									} else if( substr( $tempArgumentArray[ 1 ], 0, 7 ) == '{%like}' ) {
										$clauseValue = escape( substr( $tempArgumentArray[ 1 ], 7 ) );
										$tempClause = "`$tempArgumentArray[0]` like '%$clauseValue'";
									} else {
										$firstCharacter = '=';
										$clauseValue = escape( $tempArgumentArray[ 1 ] );
										if( is_numeric( $clauseValue ) || strtolower( ( trim( $clauseValue ) ) ) == 'now()' ) {
											$tempClause = "`$tempArgumentArray[0]` $firstCharacter $clauseValue";
										} else {
											$tempClause = "`$tempArgumentArray[0]` $firstCharacter '$clauseValue'";
										}
									}
								}
								array_push( $this->_arrayClauses, $tempClause );
							} else {
								$firstCharacter = substr( $tempArgumentArray[ 1 ], 0, 1 );
								if( '=' == $firstCharacter ) {
									$tempArgumentArray[ 1 ] = substr( $tempArgumentArray[ 1 ], 1 );
								}
								if( '<' == $firstCharacter || '>' == $firstCharacter ) {
									throw new Exception( "Abstract : '<','>' are not allowed with " . $tempArgumentArray[ 0 ] );
								}

								switch( $tempArgumentArray[ 0 ] ) {
									case 'sortOrder': {
										if( 'asc' == strtolower( $tempArgumentArray[ 1 ] ) || 'desc' == strtolower( $tempArgumentArray[ 1 ] ) ) {
											$this->_sortOrder = strtoupper( $tempArgumentArray[ 1 ] );
										} else {
											throw new Exception( 'Abstract : ' . $tempArgumentArray[ 0 ] . "'s value '" . $tempArgumentArray[ 1 ] ." is invalid. Valid values : ASC, DESC" );
										}
										break;
									} case 'quantity': {
										if( is_numeric( $tempArgumentArray[ 1 ] ) && $tempArgumentArray[ 1 ] > 0 ) {
											$this->_quantity = $tempArgumentArray[ 1 ];
											if( $this->_quantity < 1 ) {
												$this->_quantity = 1;
											}
										} else {
											throw new Exception( 'Abstract : ' . "quantity's value '" . $tempArgumentArray[ 1 ] . ' is invalid! It should be a natural number.' );
										}
										break;
									} case 'pageNumber': {
										if( is_numeric( $tempArgumentArray[ 1 ] ) && $tempArgumentArray[ 1 ] > 0 ) {
											$this->_pageNumber = $tempArgumentArray[ 1 ];
										} else {
											throw new Exception( 'Abstract : ' . "pageNumber's value '" . $tempArgumentArray[ 1 ] . ' is invalid! It should be a natural number.' );
										}
										break;
									} case 'sortColumn': {
										$this->_sortColumn = $tempArgumentArray[1];
										break;
									} case 'count': {
										if( 'y' == strtolower( $tempArgumentArray[ 1 ] ) || 'n' == strtolower( $tempArgumentArray[ 1 ] ) ) {
											$this->_count = strtoupper( $tempArgumentArray[ 1 ] );
										} else {
											throw new Exception( "$tempArgumentArray[0]'s value '$tempArgumentArray[1]' is invalid. Valid values : Y, N" );
										}
										break;
									} case 'order': {
										if('y' == strtolower($tempArgumentArray[1]) || 'n' == strtolower($tempArgumentArray[1]))
											$this->_order = strtoupper($tempArgumentArray[1]);
										else
											throw new Exception("$tempArgumentArray[0]'s value '$tempArgumentArray[1]' is invalid. Valid values : Y, N");
										break;
									} case 'sqlFunction' : {
										$sqlFunction = explode( '|', $tempArgumentArray[ 1 ] );
										if( array_search( $sqlFunction[ 0 ], $this->_validSqlFunctions ) === FALSE ) {
											throw new Exception( 'Abstract : Invalid sql function ' . $sqlFunction[ 0 ] . ' passed! Valid functions are : ' . implode( ', ', $this->_validSqlFunctions ) . '.' );
										} else {
											if( count( $sqlFunction ) == 3 ) {
												$this->_sqlFunctions[ $sqlFunction[ 0 ] ] = $sqlFunction[ 0 ] . '( ' . $sqlFunction[ 1 ] . ') as ' . $sqlFunction[ 2 ];
											} else if( count( $sqlFunction ) == 2 ) {
												$this->_sqlFunctions[ $sqlFunction[ 0 ] ] = $sqlFunction[ 0 ] . '( ' . $sqlFunction[ 1 ] . ') as ' . $sqlFunction[ 1 ];
											} else {
												throw new Exception( 'Abstract: Request is not well formed!' );
											}
										}
									}
								}
							}
						}
					}
					$this->show();
				}
			}
			catch (Exception $e)
			{
				throw $e;
			}
		}

		function show() {
			$start = ( $this->_pageNumber - 1 ) * $this->_quantity;
			$limitClause = " LIMIT $start, $this->_quantity";
			$orderClause = $this->_sortColumn ? " ORDER BY $this->_sortColumn $this->_sortOrder" : '';
			$clauseList = "";
			if( count( $this->_arrayClauses ) > 0 ) {
				$clauseList = implode( " AND ", $this->_arrayClauses );
				$clauseList = " WHERE " . $clauseList;
				if( $this->_enableForcedCondition && $this->_betweenClause ) {
					$clauseList .= ' and ' . $this->_betweenClause;
				}
			}
			$columnsList = '*';
			if( count( $this->_sqlFunctions ) > 0 ) {
				$columnsList = implode( ', ', $this->_sqlFunctions );
				$this->_dataTotalCount = 1;
				$this->_dataTotalPages = 1;
			} else {
				$countQueryString = "SELECT COUNT(*) as dataTotalCount FROM $this->_mainTable " . $clauseList;
				$queryResult = $this->_databaseConnection->fetchAllArray( $countQueryString );
				$this->_dataTotalCount = $queryResult[ 0 ][ 'dataTotalCount' ];
				$this->_dataTotalPages = ceil( $this->_dataTotalCount / $this->_quantity );
			}
			$orderClause = $this->_dataTotalCount > 1 ? $orderClause : '';
			if( $this->_count == 'N' ) {
				$dataQueryString = "SELECT $columnsList FROM $this->_mainTable " . $clauseList . $orderClause . $limitClause;
				if( $this->_order == 'N' ) {
					$dataQueryString = "SELECT $columnsList FROM $this->_mainTable " . $clauseList . $limitClause;
				}
				$queryResult = $this->_databaseConnection->fetchAllArray($dataQueryString);
				$this->_dataResultCount = count($queryResult);
				$this->_dataResultArray = $queryResult;
				if( $this->_dataResultCount > 0 ) {
					$this->_mainTablePermissibleValues = implode( ", ", array_keys( $this->_dataResultArray[ 0 ] ) );
				}
			}
		}

		function get( $keyName, $index = 0, $xindex = 1 ) {
			$range = $this->_dataResultCount > 0 ? $this->_dataResultCount - 1 : $this->_dataResultCount;
			if( $index >= $this->_dataResultCount )
				throw new Exception(gettext("Abstract : ").gettext("Your requested index ").$index.gettext(" is invalid. Range for get should be 0 - ").$range);

			return  array_key_exists( $keyName, $this->_dataResultArray[ $index ] ) ? $this->_dataResultArray[ $index ][ $keyName ] : '';
		}

		function getResultCount() {
			return $this->_dataResultCount;
		}

		function getTotalCount() {
			return $this->_dataTotalCount;
		}

		function getLastInsertedId() {
			return $this->_lastInsertedId;
		}

		function getQuantity() {
			return $this->_quantity;
		}

		protected function beforeAdd( $queryData ) {
			return $queryData;
		}

		function add() {
			$queryData = array();
			foreach( $this->_arrayUpdatedData as $column => $value ) {
				$queryData[ $column ] = trim( $value );
			}
			//$queryData[ 'createdUserId' ] =
			$queryData[ 'createdTime' ] = date( 'Y-m-d H:i:s' );
			try {
				$this->_lastInsertedId = $this->_databaseConnection->queryInsert( $this->_mainTable, $queryData );
			} catch( Exception $ex ) {
				throw $ex;
			}
		}

		function edit() {
			if( !isset( $this->_arrayUpdatedData[ 'whereClause' ] ) ) {
				throw new Exception( 'Where clause missing for update query!' );
			}
			$queryData = array();
			foreach( $this->_arrayUpdatedData as $column => $value ) {
				if( $column != 'whereClause' ) {
					$queryData[ $column ] = trim( $value );
				}
			}

			try {
				$this->_databaseConnection->QueryUpdate( $this->_mainTable, $queryData, $this->_arrayUpdatedData[ 'whereClause' ] );
			} catch (Exception $ex) {
				throw $ex;
			}
		}

		public function delete() {
			if( !isset( $this->_arrayUpdatedData[ 'whereClause' ] ) ) {
				throw new Exception( 'Where clause missing for update query!' );
			}
			try {
				$this->_databaseConnection->QueryDelete( $this->_mainTable, $this->_arrayUpdatedData[ 'whereClause' ] );
			} catch( Exception $ex ) {
				throw new Exception( 'Problem in record deletion' . $ex->getMessage() );
			}
		}

		public function getTotalPages() {
			return $this->_dataTotalPages;
		}

		function __call( $functionName, $argumentsArray ) {
			if( substr( $functionName, 0, 3 ) == 'get' ) {
				$columnName = lcfirst( substr( $functionName, 3 ) );
				return $this->get( $columnName, count( $argumentsArray ) > 0 ? $argumentsArray[ 0 ] : 0, count( $argumentsArray ) > 1 ? $argumentsArray[ 1 ] : 0 );
			} else {
				throw new Exception( "Call to undefinded function '$functionName'!" );
			}
		}

		function getRecords( $index = false ) {
			if( $index === false ) {
				return $this->_dataResultArray;
			} else {
				$range = $this->_dataResultCount > 0 ? $this->_dataResultCount - 1 : $this->_dataResultCount;
				if( $index >= $this->_dataResultCount ) {
					throw new Exception( gettext( "Abstract : " ) . gettext( "Your requested index " ) . $index . gettext( " is invalid. Range for get should be 0 - " ) . $range );
				}
				return $this->_dataResultArray[ $index ];
			}
		}

		private function getClassName() {
			$tableName = ucfirst( str_replace( '_rel', '', str_replace( 'cms_', '', $this->_mainTable ) ) );
			$className = '';
			$i = 0;
			while( $i < strlen( $tableName ) ) {
				if( substr( $tableName, $i, 1 ) == '_' ) {
					$className .= ucfirst( substr( $tableName, ++$i, 1 ) );
				} else {
					$className .= substr( $tableName, $i, 1 );
				}
				$i++;
			}
			return 'SumoActivity_Db_' . $className;
		}

		public function fillData( $record ) {
			$className = $this->getClassName();
			$caseIsFor = 'add';
			if( !is_array( $this->_foreignKey ) ) {
				if( !isset( $record[ $this->_foreignKey ] ) ) {
					throw new Exception( 'Unable to process data!' );
				}
				$primaryKeyValue = $record[ $this->_foreignKey ];
				$selfObj = new $className();
				$selfObj->set( $this->_foreignKey . "||$primaryKeyValue", 'count||Y' );
				if( $selfObj->getTotalCount() > 0 ) {
					$caseIsFor = 'edit';
				}
			} else {
				$selfObj = new $className();
				$args = array();
				foreach( $this->_foreignKey as $column ) {
					if( !isset( $record[ $column ] ) ) {
						throw new Exception( 'Unable to process data!' );
					} else {
						$args[] = "\"$column||" . addslashes( $record[ $column ] ) . '"';
					}
				}
				$args = implode( ', ', $args );
				eval( '$selfObj->set( ' . $args . ' );' );
				if( $selfObj->getTotalCount() > 0 ) {
					$caseIsFor = 'edit';
				}
			}
			$this->_arrayUpdatedData = $record;
			$this->_task = $caseIsFor;
			$this->$caseIsFor();
			return $caseIsFor;
		}
	}